﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using ESoft.Extensions;
using Microsoft.EntityFrameworkCore;

namespace ESoft.DataAccess.EfCore
{
    /// <summary>
    /// SQL扩展类
    /// </summary>
    public static class SqlQueryExtensions
    {
        /// <summary>
        /// 执行SQL查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="db"></param>
        /// <param name="query"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static List<T> ExecuteQuery<T>(this DataContext db, string query, params object[] parameters) where T : class, new()
        {
            using (var command = db.Database.GetDbConnection().CreateCommand())
            {
                command.CommandText = query;
                command.CommandType = CommandType.Text;
                if (parameters != null)
                    foreach (var p in parameters)
                        command.Parameters.Add(p);
                db.Database.OpenConnection();

                using (var reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    var lst = new List<T>();
                    var lstColumns = new T().GetType().GetProperties(BindingFlags.DeclaredOnly | BindingFlags.Instance | BindingFlags.Public | BindingFlags.NonPublic).ToList();
                    while (reader.Read())
                    {
                        var newObject = new T();
                        for (var i = 0; i < reader.FieldCount; i++)
                        {
                            var name = reader.GetName(i);
                            PropertyInfo prop = lstColumns.FirstOrDefault(a => a.Name.ToLower().Equals(name.ToLower()));
                            if (prop == null)
                            {
                                continue;
                            }
                            var val = reader.IsDBNull(i) ? null : reader[i];
                            newObject.SetModelValue(prop, val);
                        }
                        lst.Add(newObject);
                    }
                    command.Parameters.Clear();

                    return lst;
                }
            }
        }

        /// <summary>
        /// SQL获取信息
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="db"></param>
        /// <param name="query"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static async Task<List<T>> ExecuteQueryAsync<T>(this DataContext db, string query, params object[] parameters) where T : class, new()
        {
            using (var command = db.Database.GetDbConnection().CreateCommand())
            {
                command.CommandText = query;
                command.CommandType = CommandType.Text;

                if (parameters != null)
                    foreach (var p in parameters)
                        command.Parameters.Add(p);

                await db.Database.OpenConnectionAsync();

                using (var reader = await command.ExecuteReaderAsync(CommandBehavior.CloseConnection))
                {
                    var lst = new List<T>();
                    var lstColumns = new T().GetType().GetProperties(BindingFlags.DeclaredOnly | BindingFlags.Instance | BindingFlags.Public | BindingFlags.NonPublic).ToList();
                    while (await reader.ReadAsync())
                    {
                        var newObject = new T();
                        for (var i = 0; i < reader.FieldCount; i++)
                        {
                            var name = reader.GetName(i);
                            PropertyInfo prop = lstColumns.FirstOrDefault(a => a.Name.ToLower().Equals(name.ToLower()));
                            if (prop == null)
                            {
                                continue;
                            }
                            var val = reader.IsDBNull(i) ? null : reader[i];
                            newObject.SetModelValue(prop, val);
                        }
                        lst.Add(newObject);
                    }
                    return lst;
                }
            }
        }

        /// <summary>
        /// 异步获取首值
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="context"></param>
        /// <param name="rawSql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static T ExecuteScalar<T>(this DbContext context, string rawSql,
          params object[] parameters)
        {
            var conn = context.Database.GetDbConnection();
            using (var command = conn.CreateCommand())
            {
                command.CommandText = rawSql;
                if (parameters != null)
                    foreach (var p in parameters)
                        command.Parameters.Add(p);
                conn.Open();
                return (T)Convert.ChangeType(command.ExecuteScalar(), typeof(T));
            }
        }

        /// <summary>
        /// 异步获取首值
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="context"></param>
        /// <param name="rawSql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static async Task<T> ExecuteScalarAsync<T>(this DbContext context, string rawSql,
          params object[] parameters)
        {
            var conn = context.Database.GetDbConnection();
            using (var command = conn.CreateCommand())
            {
                command.CommandText = rawSql;
                if (parameters != null)
                    foreach (var p in parameters)
                        command.Parameters.Add(p);
                await conn.OpenAsync();
                return (T)await command.ExecuteScalarAsync();
            }
        }
    }
}
